Create Dimensions and Fact Tables in OLAP Database and Populate the Dimension and Fact Tables 13

Download the SQL File attached to the

Run Script that is attached to Dimensions and Fact Tables in OLAP Database. Once ran, the tables will be made up of these columns.

After creating the Dimensions and Fact tables in OLAP Database

Create a store procedure in the SQL Server to generate and load records into DimDate and Execute it for the date range from 01/01/2010 to 12/31/2020

Stored Procedure to populate DimDate, attached to the

To populating each Dimension table, Create a store procedure in SQL Server by using Merge Statement to Insert and update that Dimension table

For Merge Statement Stored Procedures, SQL files attached to the

Create a Package in SSIS and use Execute SQL Task for each Dimension table to execute relevant Stored Procedure and Populate that Dimension table accordingly

Create another Package in SSIS to load Fact tables,
Drag and drop a Data Flow Task to Load "FactNumberOfIncident"
Inside Data Flow Task, use OLE DB Source and add OLE DB connection manager to ODS database and use "ShootingIncident" table
Using 7 Lookup Transformation and configure them to populate IncidentKey, LocationKey, DistrictKey, RaceKey, HandlingUnitKey, WeaponKey, DateKey
Use another Lookup for FactIncidentKey and configure it accordingly
Use Derived Column Transformation to add RecordFlag and Effective Date Columns
Connect "Lookup No Match Output" to Derived Column
Use OLE DB Destination to insert new records from Derived Column output to Fact table
Connect "Lookup Match Output" to an OLE DB command and configure it to update existing records

Execute packages to populate the Dimensions and Fact tables with the data

The Dimensions and Fact tables are populated with the data